package dao;

import modle.AthleteSignUpRecord;
import modle.JDBCUtil;

import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;


public class SignUpRecordDao {
    private Connection connection = null;

    public boolean insertRecord(int si_id,int a_id,String bestGrade) {
        PreparedStatement pstmt = null;//声明一个PreparedStatement对象并将其初始化为null
        String sql = "INSERT\n" +
                "INTO signuprecord(SI_ID,A_ID,SR_BestGrade,SR_Time,SR_Status)\n" +
                "VALUES\n" +
                "(?, ?, ?, ?, ?)";//参数占位符
        ResultSet rs = null;
        JDBCUtil db = new JDBCUtil();
        try{
            connection = db.getConn();
            pstmt = connection.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            //ResultSet.TYPE_SCROLL_INSENSITIVE：游标可以前后移动，结果集对创建结果集后其他人对数据库所做的更改不敏感
            // 结果集是只读的，这是默认的并发类型
            // 获取需要存储的本地时间

            LocalDateTime localDateTime = LocalDateTime.now();
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            String formattedDateTime = localDateTime.format(formatter);

            pstmt.setInt(1, si_id);
            pstmt.setInt(2, a_id);
            pstmt.setString(3, bestGrade);
            pstmt.setString(4, formattedDateTime);
            pstmt.setInt(5, 0);
            pstmt.executeUpdate();
            pstmt.close();
            connection.close();
        }catch (SQLException e) {
            //e.printStackTrace();
            return false;
        }
        catch (NullPointerException e){
            return false;
        }finally{
            try{
                if (connection != null && (!connection.isClosed())){
                    connection.close();
                }
            }catch(SQLException e){
                //e.printStackTrace();
            }
        }
        return true;
    }
    public List<AthleteSignUpRecord> getSearchSignUpRecord(int a_id, int college, int item, int status, String name){
        List<AthleteSignUpRecord> list = new ArrayList<AthleteSignUpRecord>();
        PreparedStatement pstmt = null;//声明一个PreparedStatement对象并将其初始化为null
        StringBuilder sql = new StringBuilder("select * from v_athlete_signuprecords WHERE 1=1");//参数占位符
        if(a_id != -100)
            sql.append(" AND A_ID = ?");
        if(college != -100)
            sql.append(" AND A_College_ID = ?");
        if(item != -100)
            sql.append(" AND SI_ID = ?");
        if(status != -100)
            sql.append(" AND SR_Status = ?");
        if(name != null)
            sql.append(" AND A_Name LIKE ?");
        sql.append(" ORDER BY SR_Status ASC");//升序排序

        ResultSet rs = null;
        try{
            connection = JDBCUtil.getConn();
            pstmt = connection.prepareStatement(sql.toString(),ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            //ResultSet.TYPE_SCROLL_INSENSITIVE：游标可以前后移动，结果集对创建结果集后其他人对数据库所做的更改不敏感
            // 结果集是只读的，这是默认的并发类型
            int index = 1;
            if(a_id != -100){
                pstmt.setInt(index, a_id);
                index++;
            }
            if(college != -100){
                pstmt.setInt(index, college);
                index++;
            }
            if(item != -100){
                pstmt.setInt(index, item);
                index++;
            }
            if(status != -100){
                pstmt.setInt(index, status);
            }
            if(name != null){
                pstmt.setString(index, name);
            }
            rs = pstmt.executeQuery();
            int num = 1;//项目序号
            while (rs.next()) {
                Calendar cal = Calendar.getInstance();
                // 获取时间戳信息
                Timestamp rtimestamp = rs.getTimestamp("SR_Time");
                Timestamp atimestamp = rs.getTimestamp("SR_Audit_Time");
                Timestamp anewTimestamp = null;
                Timestamp rnewTimestamp = null;
                if(atimestamp != null){
                    // 将timestamp转换成Calendar对象
                    cal = Calendar.getInstance();
                    cal.setTimeInMillis(atimestamp.getTime());

                    // 对Calendar对象进行处理，减少8个小时
                    cal.add(Calendar.HOUR_OF_DAY, -8);

                    // 使用Calendar对象创建一个新的timestamp对象
                    anewTimestamp = new java.sql.Timestamp(cal.getTime().getTime());
                }

                cal.setTimeInMillis(rtimestamp.getTime());
                // 对Calendar对象进行处理，减少8个小时
                cal.add(Calendar.HOUR_OF_DAY, -8);

                // 使用Calendar对象创建一个新的timestamp对象
                rnewTimestamp = new java.sql.Timestamp(cal.getTime().getTime());

                AthleteSignUpRecord athleteSignUpRecord = new AthleteSignUpRecord();
                athleteSignUpRecord.setId(rs.getInt("SR_ID"));
                athleteSignUpRecord.setNum(num++);
                athleteSignUpRecord.setName(rs.getString("A_NAME"));
                athleteSignUpRecord.setNo(rs.getString("A_No"));
                athleteSignUpRecord.setEvent(rs.getString("SI_NAME"));
                athleteSignUpRecord.setSignUpDate(rnewTimestamp);
                athleteSignUpRecord.setStatus(rs.getString("SR_Status"));
                athleteSignUpRecord.setAuditDate(anewTimestamp);
                athleteSignUpRecord.setBestGrade(rs.getString("SR_BestGrade"));
                list.add(athleteSignUpRecord);
            }
            pstmt.close();
            connection.close();
        }catch (SQLException e) {
            //e.printStackTrace();
        }
        catch (NullPointerException e){
            return null;
        }finally{
            try{
                if (connection != null && (!connection.isClosed())){
                    connection.close();
                }
            }catch(SQLException e){
                //e.printStackTrace();
            }
        }
        return list;
    }
    public boolean deleteSignUpRecord(int sr_id){
        PreparedStatement pstmt = null;//声明一个PreparedStatement对象并将其初始化为null
        String sql = "DELETE FROM signuprecord where sr_id = ? \n";//参数占位符
        ResultSet rs = null;
        JDBCUtil db = new JDBCUtil();
        try{
            connection = db.getConn();
            pstmt = connection.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            //ResultSet.TYPE_SCROLL_INSENSITIVE：游标可以前后移动，结果集对创建结果集后其他人对数据库所做的更改不敏感
            // 结果集是只读的，这是默认的并发类型
            // 获取需要存储的本地时间

            pstmt.setInt(1, sr_id);
            pstmt.executeUpdate();
            pstmt.close();
            connection.close();
        }catch (SQLException e) {
            //e.printStackTrace();
            return false;
        }
        catch (NullPointerException e){
            return false;
        }finally{
            try{
                if (connection != null && (!connection.isClosed())){
                    connection.close();
                }
            }catch(SQLException e){
                //e.printStackTrace();
            }
        }
        return true;
    }
    public String approvedSignUpRecord(int sr_id){
        String message;
        PreparedStatement pstmt = null;//声明一个PreparedStatement对象并将其初始化为null
        String sql = "CALL C_P_Audit_SignUpRecord(?);";//参数占位符
        ResultSet rs = null;
        JDBCUtil db = new JDBCUtil();
        try {
            Connection connection = db.getConn();
            pstmt = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            //ResultSet.TYPE_SCROLL_INSENSITIVE：游标可以前后移动，结果集对创建结果集后其他人对数据库所做的更改不敏感
            // 结果集是只读的，这是默认的并发类型
            pstmt.setInt(1, sr_id);
            pstmt.execute();
            pstmt.close();
            connection.close();
            message = "OK";
        }catch (SQLException e) {
            message = e.getMessage();
        }finally {

        }
        return message;
    }
    public String notApprovedSignUpRecord(int sr_id){
        String message = "";
        PreparedStatement pstmt = null;//声明一个PreparedStatement对象并将其初始化为null
        String sql = "CALL C_P_Audit_NotApproved_SignUpRecord(?);";//参数占位符
        ResultSet rs = null;
        JDBCUtil db = new JDBCUtil();
        try {
            Connection connection = db.getConn();
            pstmt = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            //ResultSet.TYPE_SCROLL_INSENSITIVE：游标可以前后移动，结果集对创建结果集后其他人对数据库所做的更改不敏感
            // 结果集是只读的，这是默认的并发类型
            pstmt.setInt(1, sr_id);
            pstmt.execute();
            pstmt.close();
            connection.close();
            message = "OK";
        }catch (SQLException e) {
            message = e.getMessage();
        }finally {

        }
        return message;
    }
    public String quashApprovedSignUpRecord(int sr_id){
        String message = "";
        PreparedStatement pstmt = null;//声明一个PreparedStatement对象并将其初始化为null
        String sql = "CALL C_P_Audit_Quash_SignUpRecord(?);";//参数占位符
        ResultSet rs = null;
        JDBCUtil db = new JDBCUtil();
        try {
            Connection connection = db.getConn();
            pstmt = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            //ResultSet.TYPE_SCROLL_INSENSITIVE：游标可以前后移动，结果集对创建结果集后其他人对数据库所做的更改不敏感
            // 结果集是只读的，这是默认的并发类型
            pstmt.setInt(1, sr_id);
            pstmt.execute();
            pstmt.close();
            connection.close();
            message = "OK";
        }catch (SQLException e) {
            message = e.getMessage();
        }finally {

        }
        return message;
    }
}